**************************************************************************************
* Bemærk at denne fil kun er til orientering om databaseændringer til denne release. *
* Filen MÅ IKKE afvikles!                                                            *
**************************************************************************************


IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'WebHoldReservation' and role = 'kursist' ) THEN
   ALTER TABLE WebHoldReservation DROP FOREIGN KEY kursist;
END IF;
IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'WebHoldReservation' AND cname = 'personnummer') THEN
  ALTER TABLE WebHoldReservation DROP personnummer;
END IF;

-- Scoreark til brug for Modultestversion 10 LUD-77704
IF NOT EXISTS (SELECT 1 FROM ScorearkSkabelon WHERE undervisning_id in (select id from undervisning where modul = 3 and uddannelse = 3) and fraModultestVersion = 10 ) THEN
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 3 and uddannelse = 3), 1, 10,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 3 and uddannelse = 3), 2, 10,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 3 and uddannelse = 3), 3, 10,  getDate(),'System', 'X' );
COMMIT;
END IF;
IF NOT EXISTS (SELECT 1 FROM ScorearkSkabelon WHERE undervisning_id in (select id from undervisning where modul = 4 and uddannelse = 3) and fraModultestVersion = 10 ) THEN
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 3), 1, 10,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 3), 2, 10,  getDate(),'System', 'X' );
  INSERT INTO "DBA"."ScorearkSkabelon" ("version","undervisning_id","testsaet", "fraModultestVersion","sidstGemtDato","sidstGemtAf","systemOprettet")
    VALUES(0, (select id from undervisning where modul = 4 and uddannelse = 3), 3, 10,  getDate(),'System', 'X' );
COMMIT;
END IF;


UPDATE UndervisningPeriode p
SET p.timer = 0.00
WHERE p.timer is null;

UPDATE UndervisningPeriode p
SET p.ugentligTimetal = 0.00
WHERE p.ugentligTimetal is null;

UPDATE UndervisningPeriode p
SET p.flexLaererLektioner = 0.00
WHERE p.flexLaererLektioner is null;

UPDATE UndervisningPeriode p
SET p.kursisttid = 0.00
WHERE p.kursisttid is null;

UPDATE UndervisningPeriode p
SET p.apparatOpstilling = 0.00
WHERE p.apparatOpstilling is null;

UPDATE UndervisningPeriode p
SET p.forberedelsesfaktor = 0.00
WHERE p.forberedelsesfaktor is null;

UPDATE UndervisningPeriode p
SET p.retteTillaeg = 0.00
WHERE p.retteTillaeg is null;
COMMIT;

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'PDFOgEmailSkabelon' and cname = 'pdfDokument_id') THEN
  ALTER TABLE PDFOgEmailSkabelon MODIFY pdfDokument_id NULL;

  IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'PDFOgEmailSkabelon' AND role = 'pdfDokument') THEN
    ALTER TABLE PDFOgEmailSkabelon DROP CONSTRAINT pdfDokument;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'PDFOgEmailSkabelon' AND role = 'pdfDokument') THEN
    ALTER TABLE PDFOgEmailSkabelon ADD CONSTRAINT pdfDokument FOREIGN KEY (pdfDokument_id) REFERENCES Dokument(id) ON DELETE CASCADE;
  END IF;
END IF;

IF EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'PDFOgEmailSkabelon' and cname = 'kildeDokument_id') THEN
  ALTER TABLE PDFOgEmailSkabelon MODIFY kildeDokument_id NULL;

  IF EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'PDFOgEmailSkabelon' AND role = 'kildeDokument') THEN
   ALTER TABLE PDFOgEmailSkabelon DROP CONSTRAINT kildeDokument;
  END IF;

  IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'PDFOgEmailSkabelon' AND role = 'kildeDokument') THEN
    ALTER TABLE PDFOgEmailSkabelon ADD CONSTRAINT kildeDokument FOREIGN KEY (kildeDokument_id) REFERENCES Dokument(id) ON DELETE CASCADE;
  END IF;
END IF;

IF NOT EXISTS (SELECT 1 FROM SYS.SYSCOLUMNS WHERE tname = 'PDFOgEmailSkabelon' AND cname = 'docxDokument_id') THEN
  ALTER TABLE PDFOgEmailSkabelon ADD docxDokument_id INTEGER;

  IF NOT EXISTS (SELECT 1 FROM SYSFOREIGNKEYS WHERE foreign_tname = 'PDFOgEmailSkabelon' AND role = 'docxDokument') THEN
    ALTER TABLE PDFOgEmailSkabelon ADD CONSTRAINT docxDokument FOREIGN KEY (docxDokument_id) REFERENCES Dokument(id) ON DELETE CASCADE;
  END IF;
END IF;


COMMIT;QUIT;